SQL Log Rescue  

Using SQL Log Rescue

 See Also

SQL Log Rescue is a simple tool for viewing and recovering Microsoft® SQL Server™ 2000 data. SQL Log Rescue is particularly useful if you have accidentally deleted, inserted, or updated data in your database.

You can:

You can use SQL Log Rescue with native backups, and with backups created using SQL Backup from Red Gate Software Ltd. To find out more about SQL Backup, visit www.red-gate.com

This topic provides information about:

For an overview of the analysis procedure, see Process Overview. To follow a basic example and optionally try it out for yourself, see Worked Example: Getting Started.

Database backups and transaction logs

A brief description of the terminology used in this help is provided below; a full description is beyond the scope of this Help. For more information, refer to your SQL Server 2000 documentation and reference material.

Full backup
A full backup is a complete copy of a database onto a backup device. (This is called a Complete backup in SQL Server 2000 Enterprise Manager.)
Differential backup
A differential backup is a partial copy of a database onto a backup device. Only the changes since the last full backup was made are copied. You must create a full backup of the database before you can perform a differential backup.
Live transaction log
The live transaction log records changes made to the database. It stores sufficient information to enable changes to be undone (rolled back) or redone (rolled forward) in the event of system failure.
Each SQL Server 2000 database has at least one transaction log file, and can have multiple transaction log files spread across a number of locations. A transaction log file is used by only one database, and usually has the extension .ldf.
When an operation is performed on a database, one or more log entries are written to the live transaction log. Each log entry is labelled with a log sequence number (LSN). All log entries that are part of the same transaction are linked together and allocated the same transaction id so that they can be easily located, for example to be rolled back as a group.
A log entry sometimes records explicit 'before' and 'after' values for the operation, or sometimes it references the operation so that the values can be derived.
Transaction log backup
A transaction log backup is (in most cases) a copy of all the log records that have been written to the live transaction log since the last full database backup or the last transaction log backup (whichever was most recent).

Including database backups and transaction logs in SQL Log Rescue

SQL Log Rescue uses database backups, transaction log backups, and the live transaction log to retrieve transaction information.

When you analyze a database using SQL Log Rescue, you specify which backups you want to use. You are recommended to include the following in your project:

The full backup is required because the transaction log may not include all the necessary information should you need to undo a transaction. For example, if you update a row, even though details of the modification may be logged, information about the state of that row prior to modification may not be contained in the transaction log. Therefore, it would not be possible to undo the update.

Similarly, if a table has been created and populated prior to the start of the transaction log, the details of the individual rows will not be stored in the log. Therefore, if you subsequently drop the table in error and want to restore it, SQL Log Rescue must reference the full backup to retrieve the information about the table so that it can be reconstructed.

To achieve the best results, you should make a full backup of your database as soon as you have created it. You should then keep a set of contiguous database backups so that SQL Log Rescue has a full history of each transaction. Similarly, you should keep an unbroken chain of transaction log backups since the earliest full backup. (This is good practice even if you are not using SQL Log Rescue.)

A typical example of a fully backed up database is illustrated below.

Notice that there are no periods of time for which the transaction history has not been saved:

In this example, you would include the full backup and all the transaction log backups in your project. It would not be necessary to include any differential backups.

In the following example, Monday night's transaction log backup is not available.

In this example, you would include the full backup, Tuesday's differential backup, and the three transaction log backups since Tuesday in order to achieve a contiguous history of transactions. Note that a differential backup must always be supported with its associated full backup.

Setting up your database

To ensure a full database history, set the Recovery model to Full to ensure that all operations are fully recorded in the transaction log, and immediately make a full backup of your database to activate full recovery.

If you have an existing database, make a full backup as soon as possible. Note that transactions that occurred prior to the first full backup will not be available in SQL Log Rescue (unless they are recorded in the live transaction log).

If you have an existing database for which the Recovery model is set to Simple or Bulk-Logged, change the setting to Full, then create a full backup immediately to activate the change.

SQL Log Rescue supports database backups that are stored across multiple files ('striped' or 'split'), and multiple database backups stored in a single file. However, striped (split) transaction log backups are not supported except for backups created using SQL Backup. Native backups using raw partitions are not supported.

SQL Log Rescue can process database backups that are encrypted and compressed using SQL Backup. However, processing of the backup files takes longer for encrypted or compressed files. The greater the compression, the longer the processing will take.

Installing SQL Log Rescue on the database server

SQL Log Rescue can analyze a database only if the SQL Log Rescue extended stored procedure is installed on the database server. When you use SQL Log Rescue, you can choose to install this extended stored procedure on your local server. You can later install and uninstall the extended stored procedure on the local server or on remote servers, as required, from within SQL Log Rescue. For full details about how to do this, see Accessing Other Servers from SQL Log Rescue.

File locking

SQL Log Rescue does not require exclusive access to the database while you are viewing its transactions. Therefore, although this is not recommended, users can access the database tables.

Native backup files that you include in your project are locked for read access only and cannot be modified or deleted by other users.

Backups created using SQL Backup are extracted to temporary files when you include them in your project; therefore the original SQL Backup files are not locked once the extraction has completed.

SQL Log Rescue can undo or redo transactions whilst other users are accessing the database.

Permissions

To use SQL Log Rescue you must have permissions to execute the SQL Log Rescue extended stored procedure (xp_LogRescue) in the master database. You must also be a member of the db_owner role for the database, or a member of the sysadmin role on the server.

 

 

 

© Red Gate Software Ltd 2005. All Rights Reserved.